﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace ConexionBD
{
    class NotaBD
    {
        string id_nota;
        string titulo;
        string texto;
        int id_libreta;
        string cadena;
        string opcionC;
        public void agregarNota() { }
        public void modificarNota() { }

        public DataTable modNotOneBD()
        {
            MySqlConnection Conexion = new MySqlConnection();
            cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            MySqlDataAdapter sql = new MySqlDataAdapter("Select * from libreta", Conexion);
            DataTable dt = new DataTable();
            sql.Fill(dt);
            return dt;

        }

        public DataTable modNotTwoBD(string opcion1)
        {  
            MySqlConnection Conexion = new MySqlConnection(); 
            cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            MySqlDataAdapter sql2 = new MySqlDataAdapter("Select n.titulo from nota n, libreta l where n.id_libreta = l.id_libreta and l.nombre_libreta = " + opcion1, Conexion);
            DataTable dt2 = new DataTable();
            sql2.Fill(dt2);
            return dt2;
        
        }

        public DataTable modNotTresBD(string opcion3)
        {
            MySqlConnection Conexion = new MySqlConnection();
            cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            MySqlDataAdapter sql3 = new MySqlDataAdapter("Select texto from nota where titulo = " + opcion3, Conexion);
            DataTable dt3 = new DataTable();
            sql3.Fill(dt3);
            return dt3;
        }
        public DataTable agrNotaBD()
        {
            MySqlConnection Conexion = new MySqlConnection(); this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            MySqlDataAdapter sql = new MySqlDataAdapter("Select * from libreta", Conexion);
            DataTable dt = new DataTable();
            sql.Fill(dt);
            return dt;
        }
        public DataTable mandaUnoBD()
        {
            MySqlConnection Conexion = new MySqlConnection(); this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            MySqlDataAdapter sql = new MySqlDataAdapter("Select * from libreta", Conexion);
            DataTable dt = new DataTable();
            sql.Fill(dt);
            return dt;

        }

        public DataTable verTextoBD(string opcion2)
        {
            MySqlConnection Conexion = new MySqlConnection(); this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            opcion2 = "'" + opcion2 + "'";
            MySqlDataAdapter sql3 = new MySqlDataAdapter("Select texto from nota where titulo = " + opcion2, Conexion);
            DataTable dt3 = new DataTable();
            sql3.Fill(dt3);
            return dt3;
        }
       

        public DataTable llenar(string opcion1)
        {
            MySqlConnection Conexion = new MySqlConnection(); this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            opcion1 = "'" + opcion1 + "'";

            MySqlDataAdapter sql2 = new MySqlDataAdapter("Select n.titulo from nota n, libreta l where n.id_libreta = l.id_libreta and l.nombre_libreta = " + opcion1, Conexion);
            DataTable dt2 = new DataTable();
            sql2.Fill(dt2);
            return dt2;
        }
      

        public void agregarNota(int id_libreta, string id_nota, string titulo, string contenido)
        {
            MySqlConnection Conexion = new MySqlConnection(); this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            
            MySqlCommand myCmd = new MySqlCommand();
            myCmd.Connection = Conexion;
            MySqlDataAdapter sql = new MySqlDataAdapter("INSERT INTO nota (id_nota, titulo, texto, fecha, id_libreta) VALUES (" + id_nota + ", " + titulo + ", " + contenido + ", '2012-10-20', " + id_libreta + ");", Conexion);
            DataTable dt = new DataTable();
            sql.Fill(dt);

        }

        public void modificarNota(string tituloNew, string contenido, string tituloOld)
        {
            MySqlConnection Conexion = new MySqlConnection(); this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();

            MySqlCommand myCmd = new MySqlCommand();
            myCmd.Connection = Conexion;
            
            
            MySqlDataAdapter sql6 = new MySqlDataAdapter("UPDATE nota SET titulo = " + tituloNew + ", texto = " + contenido + " WHERE titulo = " + tituloOld, Conexion);
            DataTable dt6 = new DataTable();
            sql6.Fill(dt6);
        }

        public DataTable elimNota1BD()
        {
            MySqlConnection Conexion = new MySqlConnection(); 
            this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            MySqlCommand myCmd = new MySqlCommand();
            myCmd.Connection = Conexion;
            MySqlDataAdapter sql2 = new MySqlDataAdapter("Select * from libreta", Conexion);
            DataTable dt2 = new DataTable();
            sql2.Fill(dt2);
            return dt2;

        }

        public void eliminarNotaBD(string libreta, string nota)
        {
        MySqlConnection Conexion = new MySqlConnection(); 
            this.cadena = "Server=localhost;DataBase=evernote;uid=root;Password=;";
            Conexion.ConnectionString = cadena;
            Conexion.Open();
            MySqlCommand myCmd = new MySqlCommand();
            myCmd.Connection = Conexion;
            string nota2 = "'" + nota + "'";
             MySqlDataAdapter sql7 = new MySqlDataAdapter("Delete from nota where titulo=" + nota2 + ";", Conexion);
             DataTable dt3 = new DataTable();
             sql7.Fill(dt3);
        }

        
    }
}
